If you have seen my SQL Server tools Tips and Tricks presentation at a SQL Saturday you may remember my brief discussion on Central Management Server (CMS). A 45 to 60 minute session is not near enough time to dive into all the tools including this cool hidden gem that the Microsoft SQL Server team provided us with the SQL Server 2008 release. Even though this is a well blogged about tool a recent conversation at my new job reminded me that I needed to and wanted to blog about CMS. I run into and speak with individuals who connect with SQL Server on a regular basis and are not aware of CMS or what CMS can and can’t do for you. So let’s get this hidden gem installed and start utilizing its POWER!
The 1st thing you will need is a SQL Server 2008 or greater instance installed with MSDB available. I will be setting up and taking screen shots from my SQL Server 2012 instance for the purpose of this post. I will be setting up and configuring CMS as sysadmin on this database instance. For this post I will be using my local instance however you could also use an instance in your datacenter. There are advantages and disadvantages to both based on your working environment.
Let’s get started with the CMS setup.
Open up SQL Server Management Studio (SSMS). Dependent on how you have SSMS setup it will look something like the image below.
The next step is to open up our registered services view. This can be done by Ctrl+Alt+G (2012) or by choosing view and then clicking on registered servers.
Once completing the above you should see a screen similar to the following image.
At this point we are on the screen that allows us to register CMS. Right click on Central Management Servers choosing the Register Central Management Server….
You should see the New Server Registration screen appear. For this example I chose SQL Server Authentication , you can also use Windows Authentication when registering the server. For Server name I am using (local) to connect to my local SQL Server 2012 instance. The following example is using SQL Login cmsadmin and its secure password to authenticate into the instance. At this time I am not making any changes to the Connection Properties. In rare cases I have had the need to modify one or more of the Connection Properties. Click Test once you receive a successful connection click Save.
You should see the local instance registered underneath the Central Management Servers Folder. Note (local) can be replaced with your database server name which would appear underneath the Central Management server folder.
Underneath our local server let’s set up some server groups. Normally I start off by creating development, test, staging and production. This can vary based on your environment. I then later add additional Server groups. For example I supported a scaled out Biztalk and SSRS environment. I would create a server group for each of the scaled out environments registering the server/servers that are part of those environments. Our next step is to create our basic server groups. This can be done by right clicking over (local) which will display the image below.
Click on the new Server Group to display the next screen. I filled in Group name with Development and the Group description with information about the group. Click OK.
After creating the Server Groups you will end up with something like the following image.
The next step in the process is to register some servers to take advantage of the power of CMS. Right mouse click over the Server Group you would like to register your Database server under. When you see the following screen click on New Server Registration…
You will fill in the Server name with a server you want to access utilizing CMS. When registering a server you MUST use Windows Authentication. Click on Test and once you have a successful connection click on save.
Please read the included link for further explanation on why Windows Authentication must be used to connect. An excerpt from the site has been provided.
http://msdn.microsoft.com/en-us/library/bb934126(v=sql.110).aspx#Security
“Because the connections that are maintained by a central management server execute in the context of the user, by using Windows Authentication, the effective permissions on the registered servers might vary. For example, the user might be a member of the sysadmin fixed server role on the instance of SQL Server A, but have limited permissions on the instance of SQL Server B.”
Once you have registered your servers you can end up with something like this. Each person I run into that utilizes CMS has their own take on how they like to set the groups and registered servers. I typically find myself migrating to the following setup which is included in the image below.
Here is where it really gets cool. Let’s utilize the power of CMS. You can 1st double click on any of the registered servers and it will open a connection to that server within SSMS. You can also right click on one of the groups and open a query window. In this case let’s hover over the development group. Select new query from the selections provided and a window will open which can be used to run a provided statement against the servers registered within that Server group. Notice at the bottom of the query window we are connected to 1/1 servers. This however does not display the full power of this tool.
Execute the following query:
SELECT
SERVERPROPERTY(‘Edition’) AS [Edition],
SERVERPROPERTY(‘ProductLevel’) AS [ProductLevel],
SERVERPROPERTY(‘ProductVersion’) AS [ProductVersion]
You should see the statement return something similar to the output below:
| Edition |
ProductLevel |
ProductVersion |
| Standard Edition (64-bit) |
RTM |
11.0.2100.60 |
Not to impressive yet. Let’s kick it up a notch and see what else CMS can do for us.
Hovering over (local) or your {server name} this time right mouse click and select new query. Selecting new query from the selections provided will open a new window which will allow us to run a provided SQL statement against the servers registered within the Server groups. Notice at the bottom of the query window we are connected to 4/4 servers. The connected value will vary based on the number of servers registered. *Note you can also do this at the group level.*
Execute the following query:
SELECT
SERVERPROPERTY(‘Edition’) AS [Edition],
SERVERPROPERTY(‘ProductLevel’) AS [ProductLevel],
SERVERPROPERTY(‘ProductVersion’) AS [ProductVersion]
You should see the statement return something similar to the output below which will be dependent on your registered servers:
| Server Name |
Edition |
ProductLevel |
ProductVersion |
| StagingServer |
Standard Edition (64-bit) |
SP1 |
10.50.2500.0 |
| DevServer |
Standard Edition (64-bit) |
RTM |
11.0.2100.60 |
| TestServer |
Standard Edition |
SP3 |
9.00.4060.00 |
| ProdServer |
Enterprise Edition |
SP4 |
8.00.2066 |
In a matter of seconds I was able to pull which versions of SQL Server I had out there in my different environments. Imagine this, you have hundreds if not thousands of servers. You have become the “Accidental DBA” and know nothing about the environment you inherited and were left with just a list of servers. You have no knowledge of PowerShell. This is a quick and powerful way to extract information from your SQL Server database instances just by understanding some basic SQL statements. Seeing just how powerful this is should give you some pause and think do I really want to run this statement against our live production database? Use best practices and common sense is my best advice. Possibly think about using set transaction isolation level read uncommitted when executing your queries.
Registering CMS will store information within the MSDB database. Security to CMS can be controlled utilizing a couple of database roles within the MSDB database. ServerGroupAdministratorRole provides you the ability to administer CMS. ServerGroupReaderRole grants users the ability to connect to CMS. Two views of interest are the sysmanagement_shared_server_groups and the sysmanagement_shared_registered_servers.
The sysmanagement_shared_server_groups view will return information with respect to the groups you have created for your registered CMS instance.
You may want to execute the following statement against the view to see how this data is being stored.
SELECT server_group_id,name,description,parent_id,num_server_group_children,num_registered_server_children
FROM msdb..sysmanagement_shared_server_groups
WHERE is_system_object <> 1
| server_group_id |
name |
description |
parent_id |
num_server_group_children |
num_registered_server_children |
| 7 |
Developement |
Place Development Servers in this group. |
1 |
0 |
1 |
| 8 |
Staging |
|
1 |
0 |
1 |
| 9 |
Test |
|
1 |
0 |
1 |
| 10 |
Production |
|
1 |
0 |
1 |
The sysmanagement_shared_registered_servers view will return information with respect to the servers you have registered and the group they were registered within.
You may want to execute the following statement against the view to see how this data is being stored.
SELECT server_id,server_group_id,name,server_name,description,server_type
FROM sysmanagement_shared_registered_servers
| server_id |
server_group_id |
name |
server_name |
description |
server_type |
| 1006 |
7 |
DevServer |
DevServer |
|
0 |
| 1008 |
8 |
StagingServer |
StagingServer |
|
0 |
| 1009 |
9 |
TestServer |
TestServer |
|
0 |
| 1007 |
10 |
ProdServer |
ProdServer |
|
0 |
There are a number of procedures that are utilized to manage CMS which you can examine for yourself by executing the following query.
You can execute the following statement to view these stored procedures.
SELECT * FROM msdb.sys.sysobjects
WHERE name LIKE ‘%sysmanagement%’ AND xtype=‘P’
CMS is a power tool that the Microsoft team has provided out of the box at no cost to you. Once CMS has been setup and configured the insight into you systems and environments is priceless. One of the reasons I embrace the Microsoft SQL Server Suite is that the tools provided have been thought thru and have had time put into them. Some are good, some are ah. CMS is one of those tools that provides the DBA’s, Developers and even Management a view into our SQL world.
References utilized in the past when 1st setting up CMS:
One of the 1st links I ever hit when examining CMS
http://www.sqlskills.com/blogs/kimberly/post/SQL-Server-2008-Central-Management-Servers-have-you-seen-these.aspx
Microsoft link mention in this post
http://msdn.microsoft.com/en-us/library/bb934126(v=sql.100).aspx
This Post written while Jamming to DKM….
DBABullDog 